<?php
class C_Input extends Controller {
	var $template_mod;
	var $_db;
	var $join_db;
	var $limit = 50;

	function C_Input($template_mod = "general") {
		parent::Controller();
		$this->template_mod = $template_mod;
		$this->_db	= $GLOBALS['frame']['adodb']['db'];
	}

	function actionFillCreditAccount() {
	    $ds = new Datasource_sql();
	    $sql = array(
	        'cols'    => 'pay_payment_id',
	        'from'    => 'pay_payment');
	    $ds->setup($this->_db, $sql);
	    $res = $ds->toArray();
	    
	    foreach ($res as $item) {
	        Celini::newOrdo('PayPaymentItem')->createCreditAccountItem($item['pay_payment_id']);	        	       
	    }
	}
	
	function actionNormalizedPersonName() {
		$connection = mysql_connect('localhost','root','')
		or die("Fail to connect database");
		echo "Connected to localhost mysql server";
		mysql_select_db('his_dev');
		echo "Select his_dev database successfully";
		$sql = "SELECT person_id, name FROM person";
		$patients = mysql_query($sql);
		while ($row = mysql_fetch_array($patients)){
			$name = $this->normalizePersonName($row['name']);
			$sql = "UPDATE person set name = '" .$name ."' where person_id = ". $row['person_id'];
			mysql_query($sql);
		}
	}

	function actionNormalizeAddress(){
		$server		=	'192.168.0.61';
		$user		=	'remote';
		$pwd		=	'remote';
		$database	=	'his_dev';
		$connection = mysql_connect($server,$user,$pwd)
		or die("Fail to connect database");
		echo "Connected to $server mysql server";
		mysql_select_db($database);
		echo "Select his_dev database successfully";
		$sql = "SELECT address_id, street, ward_name AS ward FROM address";
		$dataAddress = mysql_query($sql);
		while ($row = mysql_fetch_array($dataAddress)){
			$street = Utils::sanitizeAndCaseTitle($row['street']);
			$ward = Utils::sanitizeWard($row['ward']);
			$sql = "UPDATE address
					SET street = '" .$street ."', ward_name = '" . $ward . "'
					WHERE address_id = ". $row['address_id'];
			mysql_query($sql);
		}
	}

  	private function normalizePersonName($name) {
    	return Utils::sanitizeAndCaseTitle($name);
    }

    //===========================================================
    //
    //

    // old prescription, refraction

    private function setUpServer() {
    	$server		=	'10.0.60.55';
		$user		=	'runner';
		$pwd		=	'';
		$database	=	'rhm_prod';
		$connection = mysql_connect($server,$user,$pwd) or die("Fail to connect database");
    	echo "Connected to $server mysql server <br />";

    	mysql_select_db($database) or die("Fail to select database $database");
		echo "Select $database database successfully <br />";

		mysql_query("set character set utf8");
		mysql_query("set names utf8");
		echo "Set default character set to utf8 <br />";
    }


    function actionGetNewestData_step1() {
    	$start = '2007-01-01';
    	$end = '2009-01-01';

    	echo "[Step 1.0] Seting up server <br />";
    	$this->setUpServer();

		echo "[Step 1.1] Filter medical_record from $start to $end <br />";
		$sql = "delete from medical_record where date < '$start' or date > '$end'";
		mysql_query($sql);


		echo "[Step 1.2] Select latest medical record of each patient <br />";
		$sql = "select m.*
			from medical_record m
			group by patient_id
			having m.date = max(m.date) and count(*) = 1";
		$data = mysql_query($sql);

		mysql_query("DROP TABLE IF EXISTS `ext_medical_record`");
		$sql = "CREATE TABLE IF NOT EXISTS `ext_medical_record` (
			  `medical_record_id` int(11) NOT NULL,
			  `ticket_id` int(11) default NULL,
			  `patient_id` int(11) default NULL,
			  `date` date default NULL,
			  `status` enum('open','closed') collate utf8_unicode_ci default NULL,
			  PRIMARY KEY  (`medical_record_id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
		mysql_query($sql);

		$this->insertToMedicalRecordTemp($data);
    	$sql = "select m.patient_id, m.date
			from medical_record m
			group by patient_id
			having m.date = max(m.date) and count(*) > 1";
		$data = mysql_query($sql);

		while ($row = mysql_fetch_array($data)){
			$patientId  = $row['patient_id'];
			$sql = "select m.*
					from medical_record m
					where m.patient_id = $patientId";
			$data1 = mysql_query($sql);
			$this->insertToMedicalRecordTemp($data1);
		}

		echo "[Memory] " . date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB <br />";
		echo "[Step 1] Completed. Go on step 2...";
    }


     function actionGetNewestData_step2() {
     	 echo "[Step 2.0] Seting up server <br />";
     	 $this->setUpServer();

     	 mysql_query("DROP TABLE IF EXISTS `ext_prescription`");
     	 $sql = "CREATE TABLE IF NOT EXISTS `ext_prescription` (
				  `prescription_id` int(11) NOT NULL,
				  `medical_record_id` int(11) NOT NULL,
				  `doctor_id` int(11) NOT NULL,
				  `date` date default NULL,
				  `note` varchar(255) collate utf8_unicode_ci default NULL,
				  `re_examine` varchar(100) collate utf8_unicode_ci NOT NULL,
				  PRIMARY KEY  (`prescription_id`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
		mysql_query($sql);

     	 echo "[Step 2.1] Select latest prescription <br />";
     	 $sql = "select p.*
			from ext_medical_record m
			inner join prescription p on m.medical_record_id = p.medical_record_id";
     	 $data = mysql_query($sql);
     	 echo mysql_num_rows($data). " rows selected <br />";
     	 echo "Inserting into table ext_prescription <br />";
     	 $this->insertToPrescriptionTemp($data);

     	 echo "[Memory] " . date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB <br />";
     	 echo "[Step 2] Completed. Go on step 3... <br />";
     }


	function actionGetNewestData_step3() {
		 echo "[Step 3.0] Seting up server <br />";
     	 $this->setUpServer();

     	 mysql_query("DROP TABLE IF EXISTS `ext_prescription_item`");
     	  $sql = "CREATE TABLE IF NOT EXISTS `ext_prescription_item` (
				  `prescription_item_id` int(11) NOT NULL,
				  `prescription_id` int(11) default NULL,
				  `medicine_id` int(11) default NULL,
				  `usage_title` varchar(45) collate utf8_unicode_ci default NULL,
				  `usage_num` varchar(45) collate utf8_unicode_ci default NULL,
				  `dosage` tinyint(11) default NULL,
				  `time` tinyint(11) default NULL,
				  `quantity_num` tinyint(11) default NULL,
				  `quantity_title` varchar(45) collate utf8_unicode_ci default NULL,
				  `note` varchar(255) collate utf8_unicode_ci default NULL,
				  `price` decimal(10,0) default NULL,
				  PRIMARY KEY  (`prescription_item_id`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
		mysql_query($sql);

     	 echo "[Step 3.1] Select latest prescription_item <br />";
     	 $sql = "select i.*
			from ext_prescription p
			inner join prescription_item i on p.prescription_id = i.prescription_id";
     	 $data = mysql_query($sql);
     	 echo mysql_num_rows($data). " rows selected <br />";
     	 echo "Inserting into table ext_prescription_item <br />";
     	 $this->insertToPrescriptionItemTemp($data);

     	 echo "[Memory] " . date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB <br />";
     	 echo "[Step 3] Completed. Go on step 4.. <br />";
     }


	function actionGetNewestData_step4() {
		 echo "[Step 4.0] Seting up server <br />";
     	 $this->setUpServer();

     	 mysql_query("DROP TABLE IF EXISTS `ext_encounter_refraction`");
		$sql = "CREATE TABLE IF NOT EXISTS `ext_encounter_refraction` (
			  `encounter_refraction_id` int(11) NOT NULL default '0',
			  `medical_record_id` int(11) default NULL,
			  `left_refraction_type` varchar(45) collate utf8_unicode_ci default NULL,
			  `right_refraction_type` varchar(45) collate utf8_unicode_ci default NULL,
			  `left_correct_glass` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'Kính điều chỉnh',
			  `right_correct_glass` varchar(45) collate utf8_unicode_ci default NULL,
			  `interocular_distance` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'Khoảng cách đồng tử',
			  `left_visual_acuity` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'Thị lực gần+Add',
			  `right_visual_acuity` varchar(45) collate utf8_unicode_ci default NULL,
			  `left_skiascopy` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'Skiascopy trái',
			  `right_skiascopy` varchar(45) collate utf8_unicode_ci default NULL,
			  `left_current_glass_diopter` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'Độ kính đang sử dụng',
			  `right_current_glass_diopter` varchar(45) collate utf8_unicode_ci default NULL,
			  `current_khoang_cach_tam_kinh` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'Khoảng cách tâm kính cũ',
			  `left_glass_suggestion` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'Kinh đề nghị',
			  `right_glass_suggestion` varchar(45) collate utf8_unicode_ci default NULL,
			  `note` varchar(255) collate utf8_unicode_ci default NULL,
			  `doctor_id` int(11) default NULL,
			  `doctor_updated_id` int(11) default NULL,
			  `encounter_date` datetime default NULL,
			  `last_updated` datetime default NULL,
			  PRIMARY KEY  (`encounter_refraction_id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
		mysql_query($sql);

     	 echo "[Step 4.1] Select latest encounter_refraction <br />";
     	 $sql = "select r.*
			from ext_medical_record m
			inner join encounter_refraction r on m.medical_record_id = r.medical_record_id";
     	 $data = mysql_query($sql);

     	 echo mysql_num_rows($data). " rows selected <br />";
     	 echo "Inserting into table ext_encounter_refraction <br />";
     	 $this->insertToEncounterRefractionTemp($data);

     	 echo "[Memory] " . date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB <br />";
     	 echo "[Step 4] Completed. Go on step 5... <br />";
     }


     function actionGetNewestData_step5() {
		 echo "[Step 5.0] Seting up server <br />";
     	 $this->setUpServer();

     	 mysql_query("DROP TABLE IF EXISTS `ext_encounter_common_info`");
     	 $sql = "CREATE TABLE IF NOT EXISTS `ext_encounter_common_info` (
			  `encounter_common_info_id` int(11) NOT NULL,
			  `medical_record_id` int(11) NOT NULL,
			  `left_cva` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `left_ucva` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `right_cva` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `right_ucva` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `left_javal_k1` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `left_javal_k2` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `right_javal_k1` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `right_javal_k2` varchar(45) collate utf8_unicode_ci NOT NULL,
			  `both_eyesight` varchar(45) collate utf8_unicode_ci NOT NULL,
			  PRIMARY KEY  (`encounter_common_info_id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
     	 mysql_query($sql);

     	 $sql = "select c.*
			from ext_medical_record m
			inner join encounter_common_info c on m.medical_record_id = c.medical_record_id";
		  $data = mysql_query($sql);

     	 echo mysql_num_rows($data). " rows selected <br />";
     	 echo "Inserting into table ext_encounter_common_info <br />";
     	 $this->insertToEncounterCommonInfoTemp($data);

     	 echo "[Memory] " . date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB <br />";
     	 echo "[Step 5] Completed. Go on step 6... <br />";
     }

     // TODO
	function actionGetNewestData_step6() {
     	 echo "[Step 6.0] Seting up server <br />";
     	 $this->setUpServer();

     	 mysql_query("DROP TABLE IF EXISTS `ext_encounter`");
     	 $sql = "CREATE TABLE IF NOT EXISTS `ext_encounter` (
				  `encounter_id` int(11) NOT NULL,
				  `medical_record_id` int(11) NOT NULL,
				  `symptom` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'trieu chung',
				  `left_intraocular_tension` varchar(45) collate utf8_unicode_ci default NULL COMMENT 'nhan ap',
				  `right_intraocular_tension` varchar(45) collate utf8_unicode_ci default NULL,
				  `left_ban_phan_truoc` varchar(255) collate utf8_unicode_ci default NULL,
				  `left_ban_phan_sau` varchar(255) collate utf8_unicode_ci default NULL,
				  `right_ban_phan_truoc` varchar(45) collate utf8_unicode_ci default NULL,
				  `right_ban_phan_sau` varchar(45) collate utf8_unicode_ci default NULL,
				  `chan_doan` varchar(255) collate utf8_unicode_ci NOT NULL,
				  `primary_icd10` varchar(45) collate utf8_unicode_ci default NULL,
				  `secondary_icd10_1` varchar(45) collate utf8_unicode_ci default NULL,
				  `secondary_icd10_2` varchar(45) collate utf8_unicode_ci default NULL,
				  `treatment` enum('surgery','move_to_other_hospital','prescription') collate utf8_unicode_ci default NULL COMMENT 'chi dinh dieu tri, add more later',
				  `note` varchar(255) collate utf8_unicode_ci default NULL,
				  `doctor_id` int(11) default NULL,
				  `doctor_updated_id` int(11) default NULL,
				  `encounter_date` datetime default NULL,
				  `last_updated` datetime default NULL,
				  PRIMARY KEY  (`encounter_id`)
				) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;";
		mysql_query($sql);

     	 echo "[Step 2.1] Select latest encounter <br />";
     	 $sql = "select e.*
			from ext_medical_record m
			inner join encounter e on m.medical_record_id = e.medical_record_id";
     	 $data = mysql_query($sql);
     	 echo mysql_num_rows($data). " rows selected <br />";
     	 echo "Inserting into table ext_encounter <br />";
     	 $this->insertToEncounterTemp($data);

     	 echo "[Memory] " . date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB <br />";
     	 echo "[Step 6] Completed. Congratulation, your data is ready on ext_... table. <br />";
     }

    private function insertToMedicalRecordTemp($data) {
    	while ($row = mysql_fetch_array($data)){
			$id = $row['medical_record_id'];
			$patientId = $row['patient_id'];
			$date = $row['date'];
			$sql = "INSERT INTO `ext_medical_record` (
					`medical_record_id` ,
					`ticket_id` ,
					`patient_id` ,
					`date` ,
					`status`
					)VALUES
			($id, '', $patientId, '$date', 'closed')";
			mysql_query($sql);
		}
    }


 	private function insertToPrescriptionTemp($data) {
    	while ($row = mysql_fetch_array($data)){
			$id = $row['prescription_id'];
			$medicalRecordId = $row['medical_record_id'];
			$date = $row['date'];
			$note = $row['note'];
			$doctorId = $row['doctor_id'];
			$sql = "INSERT INTO `ext_prescription` (
				`prescription_id` ,
				`medical_record_id` ,
				`doctor_id` ,
				`date` ,
				`note` ,
				`re_examine`
				) VALUES
			($id, $medicalRecordId, $doctorId, '$date', '$note', '')";
			mysql_query($sql);
		}
    }

	private function insertToEncounterTemp($data) {
    	while ($row = mysql_fetch_array($data)){
			$id = $row['encounter_id'];
			$medicalRecordId = $row['medical_record_id'];
			$chuanDoan = $row['chan_doan'];
			$doctorId = $row['doctor_id'];

			$sql = "INSERT INTO `ext_encounter` (
					`encounter_id`,
					`medical_record_id`,
					`chan_doan`,
					`doctor_id`) VALUES
					($id, $medicalRecordId, '$chuanDoan', $doctorId)";

			mysql_query($sql);
		}
    }

	private function insertToPrescriptionItemTemp($data) {
    	while ($row = mysql_fetch_array($data)){
			$id = $row['prescription_item_id'];
			$prescriptionId = $row['prescription_id'];
			$medicineId = $row['medicine_id'];
			$usageTitle = '';
			$usageNum = $row['usage_num'];
			$dosage = $row['dosage'];
			$time = $row['time'];
			$quantityNum = $row['quantity_num'];
			$quantityTitle = $row['quantity_title'];
			$sql = "INSERT INTO `ext_prescription_item` (
						`prescription_item_id` ,
						`prescription_id` ,
						`medicine_id` ,
						`usage_title` ,
						`usage_num` ,
						`dosage` ,
						`time` ,
						`quantity_num` ,
						`quantity_title` ,
						`note` ,
						`price`
				) VALUES
			($id, $prescriptionId, $medicineId, '$usageTitle', '$usageNum', '$dosage', '$time', '$quantityNum', '$quantityTitle', '', '')";
			mysql_query($sql);
		}
    }

    private function insertToEncounterRefractionTemp($data) {
	    while ($row = mysql_fetch_array($data)){
			$sql = "INSERT INTO `ext_encounter_refraction` (
				`encounter_refraction_id` ,
				`medical_record_id` ,
				`left_refraction_type` ,
				`right_refraction_type` ,
				`left_correct_glass` ,
				`right_correct_glass` ,
				`interocular_distance` ,
				`left_visual_acuity` ,
				`right_visual_acuity` ,
				`left_skiascopy` ,
				`right_skiascopy` ,
				`left_current_glass_diopter` ,
				`right_current_glass_diopter` ,
				`current_khoang_cach_tam_kinh` ,
				`left_glass_suggestion` ,
				`right_glass_suggestion` ,
				`note` ,
				`doctor_id` ,
				`doctor_updated_id` ,
				`encounter_date` ,
				`last_updated`
				)
				VALUES (
				" . $row['encounter_refraction_id'] . ","
				 . $row['medical_record_id'] . ",'"
				 . $row['left_refraction_type'] . "','"
				 . $row['right_refraction_type'] . "','"
				 . $row['left_correct_glass'] . "','"
				 . $row['right_correct_glass'] . "','"
				 . $row['interocular_distance'] . "','"
				 . $row['left_visual_acuity'] . "','"
				 . $row['right_visual_acuity'] . "','"
				 . $row['left_skiascopy'] . "','"
				 . $row['right_skiascopy'] . "','"
				 . $row['left_current_glass_diopter'] . "','"
				 . $row['right_current_glass_diopter'] . "','"
				 . $row['current_khoang_cach_tam_kinh'] . "','"
				 . $row['left_glass_suggestion'] . "','"
				 . $row['right_glass_suggestion'] . "','"
				 . $row['note'] . "','"
				 . $row['doctor_id'] . "','"
				 . "0" . "','"
				 . $row['encounter_date'] . "','"
				 . $row['last_updated'] . "')";

			mysql_query($sql);
		}
    }

    private function insertToEncounterCommonInfoTemp($data) {
    	while ($row = mysql_fetch_array($data)){
			$sql = "INSERT INTO `ext_encounter_common_info` (
					`encounter_common_info_id` ,
					`medical_record_id` ,
					`left_cva` ,
					`left_ucva` ,
					`right_cva` ,
					`right_ucva` ,
					`left_javal_k1` ,
					`left_javal_k2` ,
					`right_javal_k1` ,
					`right_javal_k2` ,
					`both_eyesight`
					) VALUES (
				" . $row['encounter_common_info_id'] . ","
				 . $row['medical_record_id'] . ",'"
				 . $row['left_cva'] . "','"
				 . $row['left_ucva'] . "','"
				 . $row['right_cva'] . "','"
				 . $row['right_ucva'] . "','"
				 . '' . "','"
				 . '' . "','"
				 . '' . "','"
				 . '' . "','"
				 . $row['both_eyesight'] . "')";
			mysql_query($sql);
    	}
    }
    //===========================================

	function actionGetDataFrom51(){

		$myServer = "LHVMP51";
		$myUser = "administrator";
		$myPass = "changeit";
		$myDB = "BVMCT_New";

		//connection to the database
		$dbhandle = mssql_connect($myServer, $myUser, $myPass)
		  or die("Couldn't connect to SQL Server on $myServer");

		//select a database to work with
		$selected = mssql_select_db($myDB, $dbhandle)
		  or die("Couldn't open database $myDB");

		// Get data from table BV_ThDonVi, import to medicine_units---------------
		$query = " SET NAMES 'utf8_unicode_ci'";
		mssql_query($query);
		$query = "SELECT ThDV_ID id, ThDV_Ten name FROM BV_ThDonVi ";
		$result = mssql_query($query);
		$numRows = mssql_num_rows($result);
		//display the results
		$sqlUnit = "INSERT INTO `medicine_unit` (`unit_id`, `unit_name`) VALUES ";
		while($row = mssql_fetch_array($result)){
			$sqlUnit = $sqlUnit."('".$row['id']."', '".$row['name']."'),";
		}

		// ------------------------------------------------------------------------

		// Get Thc_TenGoc from BV_Thuoc, insert to medicine_original_name
		$query = "SELECT distinct(Thc_TenGoc) as name FROM BV_Thuoc ";
		$result = mssql_query($query);
		$numRows = mssql_num_rows($result);
		//display the results
		$sqlOriginalName = "INSERT INTO `medicine_original_name` (`id`, `name`) VALUES ";
		$i = 1;
		while($row = mssql_fetch_array($result)){
			$sqlOriginalName = $sqlOriginalName."(".$i.", '".$row['name']."'), \n";
			$i++;
		}
		print_r($sqlOriginalName);

		//----------------------------------------------------------------------

		// ------------------------------------------------------------------------

		// Get data from BV_Thuoc, insert to medicine_original_name
		$query = "SELECT Thc_ID medicine_id, Thc_Ten name, Thc_TenGoc original_name_id, donvi.ThDV_ID donvithuoc, Thc_Deleted deleted ".
				"FROM BV_Thuoc thuoc LEFT JOIN BV_ThDonVi donvi ON (thuoc.Thc_ThDV_ID = donvi.ThDV_ID)";
		$result = mssql_query($query);
		$numRows = mssql_num_rows($result);
		//display the results
		$sqlMedicine = "INSERT INTO `medicine` (`medicine_id`, `name`, `note`, `usage_unit_id`, `deleted`) VALUES ";
		$i = 1;
		while($row = mssql_fetch_array($result)){
			$sqlMedicine = $sqlMedicine."(".$row['medicine_id'].", \"".$row['name']."\", \"".$row['original_name_id']."\", \"".$row['donvithuoc']."\", ".$row['deleted']."), \n";
			$i++;
		}
		print_r($sqlMedicine);
		// $this->_db->_execute($sqlUnit);
		//close the connection
		mssql_close($dbhandle);
	}

	function actionUpdatePatientCode(){
		$connection = mysql_connect('localhost','root','')
		or die("Fail to connect database");
		mysql_select_db('his_dev');
		$sql = "SELECT person_id FROM person";
		$patients = mysql_query($sql);
		$updateCode = '';
		while ($row = mysql_fetch_array($patients, MYSQL_NUM)){
			$updateCode = "UPDATE patient SET patient_code = '".$this->generateCode($row[0])."' WHERE person_id = '".$row[0]."';";
			mysql_query($updateCode);
		}
	}

	function actionDeleteEmptyName(){
		$connection = mysql_connect('localhost','root','')
		or die("Fail to connect database");
		mysql_select_db('his_dev');
		$sql = "SELECT person_id FROM person WHERE name = ' '";
		$patients = mysql_query($sql);
		$updateCode = '';
		while ($row = mysql_fetch_array($patients, MYSQL_NUM)){
			$updateCode = "DELETE FROM address WHERE person_id = '".$row[0]."';";
			mysql_query($updateCode);
			$updateCode = "DELETE FROM person WHERE person_id = '".$row[0]."';";
			mysql_query($updateCode);
		}
	}

	function actionImportDataFrom51() {

		$myServer = "10.0.60.51\LHVMP51";
		$myUser = "administrator";
		$myPass = "changeit";
		$myDB = "BVMCT_New";

		//connection to the database
		$dbhandle = mssql_connect($myServer, $myUser, $myPass)
		  or die("Couldn't connect to SQL Server on $myServer");

		//select a database to work with
		$selected = mssql_select_db($myDB, $dbhandle)
		  or die("Couldn't open database $myDB");


		$query = "select LyL_ID id, LyL_NgaySinh ngay, LyL_ThangSinh thang, LyL_NamSinh nam, LyL_Sex_ID gioitinh, A_1 ten, BV_LyLich.address address, tel
			from BV_LyLich
			where A_1 != '' ";
		$result = mssql_query($query);
		$numRows = mssql_num_rows($result);
		//display the results
		$pre = "INSERT INTO `person` (`person_id`, `name`, `date_of_birth`, `gender`, `address`, `phone_number`) VALUES ";
		$i = 1;

		while($item = mssql_fetch_array($result) && $i < 10){
			$row = $item;
			$tthc = $pre. "(".$row['id']. ", '". $row['ten'] ."',";
			//echo $row['ngay'].$row['thang'].$row['nam']."\n";
			if ($row['ngay'] == '' || $row['ngay'] == ' ') $row['ngay'] = '01';
			if ($row['thang'] == '' || $row['thang'] == ' ') $row['thang'] = '01';
			if ($row['nam'] == '' || $row['nam'] == ' ') $row['nam'] = '1900';
			//echo $row['ngay'].$row['thang'].$row['nam']."\n";
			$tthc .= "'". $row['nam']. "-". $row['thang']. "-". $row['ngay']. "',";
			if ($row['gioitinh'] == 1) $row['gioitinh'] = 'male';
			if ($row['gioitinh'] == 2) $row['gioitinh'] = 'female';
			$tthc .= "'" .$row['gioitinh']. "', '". $row['address']. "', '". $row['tel']. "')";
			$i++;
			//mysql_query($tthc);
		}
		echo ($tthc);
	}

	function actionGetTTHC(){
		$serverName = "(NTKVINH-NB/KIMVINH)";
		$connectionInfo = array( "Database"=>"BVMCT_New");
		$conn = sqlsrv_connect( $serverName, $connectionInfo);
		if( $conn === false )
		{
		     echo "Could not connect.<br>";
		     die( print_r( sqlsrv_errors(), true));
		}
		return;
		$myServer = "NTKVINH-NB\KIMVINH";
		$myUser = "sa";
		$myPass = "changeit";
		$myDB = "BVMCT_New";

		//connection to the database
		$dbhandle = mssql_connect($myServer, $myUser, $myPass)
		  or die("Couldn't connect to SQL Server on $myServer");

		//select a database to work with
		$selected = mssql_select_db($myDB, $dbhandle)
		  or die("Couldn't open database $myDB");

		$query = "SELECT 'insert into `address_migrate` (`street`) VALUES(', BV_LyLich.address, ');' FROM BV_LyLich  FROM BV_LyLich";
		$result = mssql_query($query);
		$numRows = mssql_num_rows($result);
		echo $numRows;
		$i = 1;
		$pre = "INSERT INTO `address_migrate` (`address_id`, `street`) VALUES ";

		$connection = mysql_connect('localhost','root','')
		or die("Fail to connect database");
		mysql_select_db('his_dev');

		while($row = mssql_fetch_array($result)){
			$add = $pre. "(".$i. ", '". $row['address'] ."')";
			$i++;
			mysql_query($add);
		}


/*		$query = "select LyL_ID id, LyL_NgaySinh ngay, LyL_ThangSinh thang, LyL_NamSinh nam, LyL_Sex_ID gioitinh, A_1 ten, BV_LyLich.address address, tel
			from BV_LyLich
			where A_1 != '' ";
		$result = mssql_query($query);
		$numRows = mssql_num_rows($result);
		//display the results
		$pre = "INSERT INTO `person` (`person_id`, `name`, `date_of_birth`, `gender`, `address`, `phone_number`) VALUES ";
		$i = 1;

		while($item = mssql_fetch_array($result)){
			$row = $item;
			$tthc = $pre. "(".$row['id']. ", '". $row['ten'] ."',";
			//echo $row['ngay'].$row['thang'].$row['nam']."\n";
			if ($row['ngay'] == '' || $row['ngay'] == ' ') $row['ngay'] = '01';
			if ($row['thang'] == '' || $row['thang'] == ' ') $row['thang'] = '01';
			if ($row['nam'] == '' || $row['nam'] == ' ') $row['nam'] = '1900';
			//echo $row['ngay'].$row['thang'].$row['nam']."\n";
			$tthc .= "'". $row['nam']. "-". $row['thang']. "-". $row['ngay']. "',";
			if ($row['gioitinh'] == 1) $row['gioitinh'] = 'male';
			if ($row['gioitinh'] == 2) $row['gioitinh'] = 'female';
			$tthc .= "'" .$row['gioitinh']. "', '". $row['address']. "', '". $row['tel']. "')";
			$i++;
			mysql_query($tthc);
		}
		$insertAddressId = "UPDATE person LEFT JOIN address ON (address.street = person.address) SET person.address_id = address.address_id";
		mysql_query($insertAddressId);
	$pty = mssql_query("sp_helpdb BVMCT_New");
		while ($row = mssql_fetch_array($pty)){
			print_r($row);
			}

*/	}

	private function generateCode($id){
		switch ($id){
			case ($id < 10):
				return "BVCT000000".$id;
			case ($id < 100 && $id >= 10):
				return "BVCT00000".$id;
			case ($id < 1000 && $id >= 100):
				return "BVCT0000".$id;
			case ($id < 10000 && $id >= 1000):
				return "BVCT000".$id;
			case ($id < 100000 && $id >= 10000):
				return "BVCT00".$id;
			case ($id < 1000000 && $id >= 100000):
				return "BVCT0".$id;
			case ($id < 10000000 && $id >= 1000000):
				return "BVCT".$id;
		}
	}

	public function actionImportRHMPayment() {
        $this->setUpServer();
        $sql    =   "
        		SELECT * FROM 
        		rhm_payment r
        			INNER JOIN rhm_treatment_instance t ON 
        			r.rhm_treatment_instance_id = t.rhm_treatment_instance_id
        		ORDER BY r.bill_no";
        
        $data   =   mysql_query($sql);
        $receiptList   =   array();
        $ticketList = array();
        while($row = mysql_fetch_array($data)) {            
        	$receiptList[$row['bill_no']][]   =   $row;
        	$ticketList[$row['ticket_id']][] = $row;        	
        }

        $paymentItemReference = array();
        $i      =   900000;
        foreach ($ticketList as $ticketId => $row) {
            $collectAmount  =   0;
            $collectRow     =   array();
            foreach($row as $item) {
                $collectAmount    +=  $item['price'];
                if (empty($collectRow)) {
                    $collectRow  =   $item;
                }            	
            }
            
            $paymentId  =   0;
            
            // 1: Create pay_payment record
            $ticketId  =   $collectRow['ticket_id'];
            $patientId =   $collectRow['patient_id'];
             
            $sql = "INSERT INTO `pay_payment`
            		(`pay_payment_id`,            		`ticket_id`, 
            		`patient_id`, 		           		`department_id`, 
            		`original_price`,             		`insurance_price`, 
            		`discount`, 	            		`patient_price`, 
            		`real_payment`,             		`enum_patient_type`, 
            		`enum_examination_type`) 
            		
            		VALUES ( 
            		
            		$i,             		$ticketId,
            		$patientId,       		8,
            		$collectAmount,   		0,
            		0,            			$collectAmount,
            		$collectAmount,    		2,
            		0)";
            echo "Insert pay_payment record id = $i ...<br />";
    		mysql_query($sql);
    		$paymentId  =   $i;
            $i++;
            
            // Create credit account                	    
    	    $date = $collectRow['date'];    	                    	
            $sql = "INSERT INTO `pay_payment_item` (
            	`pay_payment_item_id`, 		`pay_payment_id`, 
            	`service_name`, 			`service_id`, 
            	`unit`, 					`quantity`, 
            	`unit_price`,              	`original_price`, 
            	`insurance_price`,         	`discount`, 
            	`patient_price`,           	`enum_item_type`, 
            	`change_price_note`,       	`doctor_id`, 
            	`date`, 	               	`real_payment`, 
            	`deleted`) VALUES (
            	
            	$i,	                	$paymentId,
            	'Tài khoản BN',	        -1,
            	'',	                	0,
            	0,	                	0,
            	0,	                	0,
            	0,	                	-1,
            	'',	                	-1,
            	'$date',                0,
            	0
            	)";
            
            echo "Insert credit account (pay_payment_item) record_id = $i, pay_payment_id = $paymentId ... <br />";
            mysql_query($sql);
	        $i++;
	                        	
    		foreach ($row as $item) {

    		    if ($item['enum_payment_type'] == 1) {
    		        $serviceName  =   $item['service_name'];
    		        $serviceId    =   $item['service_id'];
    		        $quantity     =   ($item['quantity'] > 0) ? $item['quantity'] : 1;    		        
    		        $price        =   $item['price'];
    		        $itemType     =   $item['enum_service_type'];
    		        $doctorId     =   $item['doctor_id'];
    		        $date         =   $item['date'];

    		        $sql = "INSERT INTO `pay_payment_item` (
	                	`pay_payment_item_id`, 		`pay_payment_id`, 
	                	`service_name`, 			`service_id`, 
	                	`unit`, 					`quantity`, 
	                	`unit_price`,              	`original_price`, 
	                	`insurance_price`,         	`discount`, 
	                	`patient_price`,           	`enum_item_type`, 
	                	`change_price_note`,       	`doctor_id`, 
	                	`date`, 	               	`real_payment`, 
	                	`deleted`) VALUES (
	                	
	                	$i,	                	$paymentId,
	                	'$serviceName',	        $serviceId,
	                	'',	                	$quantity,
	                	0,	                	$price,
	                	0,	                	0,
	                	$price,	                $itemType,
	                	'',	                	$doctorId,
	                	'$date',                $price,
	                	0
	                	)";
	                echo "Insert pay_payment_item record id = $i, pay_payment_id = $paymentId ...<br />";	                 	
                	mysql_query($sql);
                	$paymentItemReference[$item['bill_no']][$serviceId] = $i;                	                	
                	$i++;
    		    }
            }
        }
        
        foreach ($receiptList as $receiptCode => $row) {
            $collectAmount  =   0;
            $collectRow     =   array();
            foreach($row as $item) {
                $collectAmount    +=  $item['price'];
                if (empty($collectRow)) {
                    $collectRow  =   $item;
                }            	
            }
            
            $receiptId = 0;
            
            $ticketId = $collectRow['ticket_id'];
            $patientId = $collectRow['patient_id'];  
            $date = $collectRow['date'];                      
            $patientCode = Celini::newOrdo('Patient', $patientId)->get('patient_code');
            
            $cashierId = 1061049;
            $cashierName = 'Lê Thanh Trúc'; 
                        
            $sql = "INSERT INTO `pay_receipt` (
            	`pay_receipt_id`, 	`ticket_id`, 
            	`patient_id`, 		`patient_code`, 
            	`cashier_id`, 		`cashier_name`, 
            	`amount`, 			`note`, 
            	`created_time`, 	`receipt_code`, 
            	`enum_payment_type`, `referenced_receipt`) 
            	
            	VALUES
            	($i,	    $ticketId,
            	$patientId,	'$patientCode',
            	$cashierId,	'$cashierName',
            	$collectAmount, '', 
            	'$date','$receiptCode',
            	1, '')";

            echo "Insert pay_receipt record id = $i ...<br />";	
            mysql_query($sql);
            
            $receiptId = $i;            
            $i++;
            foreach($row as $item) {
                $serviceName  =   $item['service_name'];
                $serviceId    =   $item['service_id'];                
                $quantity     =   ($item['quantity'] > 0) ? $item['quantity'] : 1;
                $price        =   $item['price'];
                $itemType     =   $item['enum_service_type'];
                $doctorId     =   $item['doctor_id'];                
                $date         =   $item['date'];
                $enumItemType =   $item['enum_service_type'];
                $referenceId  =   $paymentItemReference[$receiptCode][$serviceId]; 
                $sql = "INSERT INTO `pay_receipt_item` (
                	`pay_receipt_item_id`, 	`pay_receipt_id`, 
                	`service_id`, 			`service_name`, 
                	`quantity`, 			`doctor_id`, 
                	`amount`, 				`pay_payment_item_id`, 
                	`enum_item_type`, 		`deleted`) 
                	VALUES 
                	($i,        $receiptId,
                	$serviceId,	'$serviceName',
                	$quantity,	$doctorId,
                	$price,	$referenceId, 
                	$enumItemType, 0)";             
               	echo "Insert pay_receipt_item record id = $i, pay_receipt_id = $receiptId, pay_payment_item_id = $referenceId ...<br />";   
                mysql_query($sql);	
                $i++;
            }
        }
	}// End function import payment
                
	/**
	TRUNCATE TABLE `pay_payment`;
	TRUNCATE TABLE `pay_payment_item`;
	TRUNCATE TABLE `pay_receipt`;
	TRUNCATE TABLE `pay_receipt_item`; 
	 */
}













